/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jdbcdaos;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import javax.sql.DataSource;
import jdbcpojos.Product;
import jdbcpojos.Purchase;
import jdbcpojos.PurchaseDetail;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author piotrek
 */
@Repository
public class PurchaseDao {

	private JdbcTemplate jdbcTemplate;
	
	public PurchaseDao(){
		
	}

	@Autowired
	public void setDataSource(DataSource dataSource) {

		this.jdbcTemplate = new JdbcTemplate(dataSource);

	}

	/**
	 * Dodawanie nowego zakupu przez Jdbc
	 * @return
	 */
	public boolean addPurchase( Purchase p ){

		int id = this.jdbcTemplate.queryForInt("SELECT purchase_seq.nextval from dual");
		this.jdbcTemplate.update("INSERT INTO purchase( id_purchase, cost, id_customer, purchase_date ) VALUES( ?, ?, ?, ? )", new Object[] { id, p.getCost(), p.getCustomer().getId_customer(), p.getPurchaseDate()});
		/**
		 * zapis purchaseDetails
		 */
		for( PurchaseDetail pd : p.getPurchaseDetails() ){
			int id_purchase_detail = this.jdbcTemplate.queryForInt("SELECT purchase_detail_seq.nextval from dual");
			this.jdbcTemplate.update("INSERT INTO purchase_detail ( id_purchase_detail, id_product, id_purchase,amount ) VALUES ( ?, ?, ?, ? )", new Object[]{ id_purchase_detail, pd.getProduct().getIdProduct(), id, pd.getAmount()});

		}

		return true;

	}

	/**
	 * Dodawanie nowego zakupu przez Jdbc, z kosztami przesylki - test bufora 1 poziomu
	 * @return
	 */
	public boolean addPurchaseWithShipment( Purchase p ){

		int id = this.jdbcTemplate.queryForInt("SELECT purchase_seq.nextval from dual");
		this.jdbcTemplate.update("INSERT INTO purchase( id_purchase, cost, id_customer, purchase_date ) VALUES( ?, ?, ?, ? )", new Object[] { id, p.getCost(), p.getCustomer().getId_customer(), p.getPurchaseDate()});
		/**
		 * zapis purchaseDetails
		 */
		int numOfProducts = 0;
		for( PurchaseDetail pd : p.getPurchaseDetails() ){
			int id_purchase_detail = this.jdbcTemplate.queryForInt("SELECT purchase_detail_seq.nextval from dual");
			this.jdbcTemplate.update("INSERT INTO purchase_detail ( id_purchase_detail, id_product, id_purchase,amount ) VALUES ( ?, ?, ?, ? )", new Object[]{ id_purchase_detail, pd.getProduct().getIdProduct(), id, pd.getAmount()});
			numOfProducts++;
		}

		/**
		 * Policz ilosc zakupow i wstaw odpowiednia kwote do purchase i produkt z liczba do purchase details
		 */
		this.jdbcTemplate.update("UPDATE purchase SET cost = cost + ? WHERE id_purchase = ?",
									new Object[]{p.getCost() + (10*numOfProducts), id } );

		int id_purchase_detail = this.jdbcTemplate.queryForInt("SELECT purchase_detail_seq.nextval from dual");
		this.jdbcTemplate.update("INSERT INTO purchase_detail ( id_purchase_detail, id_product, id_purchase,amount ) VALUES ( ?, ?, ?, ? )",
					new Object[]{ id_purchase_detail, 42, id, numOfProducts});

		return true;

	}

	/**
	 * @param p
	 * @return
	 */
	public boolean updatePurchase( Purchase p ){

		this.jdbcTemplate.update("UPDATE purchase SET cost = ? WHERE id_purchase = ?",
				new Object[]{p.getCost(), p.getIdPurchase()} );

		for( PurchaseDetail pd: p.getPurchaseDetails() ){

			this.jdbcTemplate.update("UPDATE purchase_detail SET amount = ? WHERE id_purchase_detail = ?",
					new Object[]{ pd.getAmount(), pd.getIdPurchaseDetail() });

		}

		return true;

	}

	/**
	 * @param p
	 * @return
	 */
	@Transactional
	public boolean updateTransactionalPurchase( Purchase p ){

		this.jdbcTemplate.update("UPDATE purchase SET cost = ? WHERE id_purchase = ?",
				new Object[]{p.getCost(), p.getIdPurchase()} );

		for( PurchaseDetail pd: p.getPurchaseDetails() ){

			this.jdbcTemplate.update("UPDATE purchase_detail SET amount = ? WHERE id_purchase_detail = ?",
					new Object[]{ pd.getAmount(), pd.getIdPurchaseDetail() });

		}

		return true;

	}

/**
 * Pobierz dane zakupu
 * @param id
 * @return Purchase
 * @throws SQLException
 * @throws NamingException
 */
public Purchase getPurchase( int id ) throws SQLException, NamingException{

	Purchase purchase = null;
	final DateFormat df = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.S");

	purchase =
		this.jdbcTemplate.queryForObject(
		"SELECT "
			+ "p.cost, p.purchase_date "
		+ "FROM "
			+ "purchase p "
		+ "WHERE "
			+ "p.id_purchase = ?",
		new Object[]{ id },
		new RowMapper<Purchase>() {

			public Purchase mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Purchase p = new Purchase();
				p.setCost( Double.parseDouble( rs.getString("cost") ) );

				try {
					p.setPurchaseDate(df.parse(rs.getString("purchase_date")));
				} catch (ParseException ex) {
					Logger.getLogger(PurchaseDao.class.getName())
							.log(Level.SEVERE, null, ex);
				}

				return p;
			}

		 }

		);

	List<PurchaseDetail> purchaseDetails =
		this.jdbcTemplate.query(
			"SELECT "
				+ "pd.id_purchase_detail, pr.name, pr.description "
			+ "FROM "
				+ "purchase_detail pd, product pr "
			+ "WHERE pd.id_purchase = ? "
				+ "AND pd.id_product = pr.id_product",
			new Object[]{ id },
			new RowMapper<PurchaseDetail>() {

				public PurchaseDetail mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					PurchaseDetail pd = new PurchaseDetail();

					pd.setIdPurchaseDetail( rs.getLong("id_purchase_detail") );

					Product product = new Product();
					product.setName( rs.getString("name") );
					product.setDescription( rs.getString("description") );

					pd.setProduct(product);

					return pd;
				}

			 }

			);

	purchase.setPurchaseDetails( purchaseDetails );

	return purchase;

}


}
